using System;
using System.Collections.Generic;
using System.Text;

namespace Common
{
    /// <summary>
    /// 
    /// </summary>
    public class SqlCommandUtil
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="tbl"></param>
        /// <param name="cols"></param>
        /// <returns></returns>
        public static string CreateSelectStatement(SchemaObject tbl, TableColumn[] cols)
        {
            System.Text.StringBuilder buffer = new StringBuilder();
            buffer.Append("Select \n");
            for (int i = 0; i < cols.Length; i++)
            {
                buffer.Append(StoredProcUtil.GetProviderSpecificLeftEscape(tbl.Provider) +
                    cols[i].ColumnName +
                    StoredProcUtil.GetProviderSpecificRightEscape(tbl.Provider));
                if (i < cols.Length - 1)
                {
                    buffer.Append(",\n");
                }
            }
            buffer.Append("\n");
            buffer.Append("From " +
                StoredProcUtil.GetProviderSpecificLeftEscape(tbl.Provider) +
                tbl.TableName +
                StoredProcUtil.GetProviderSpecificRightEscape(tbl.Provider) +
                "\n");
            return buffer.ToString();

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tbl"></param>
        /// <param name="cols"></param>
        /// <param name="filters"></param>
        /// <returns></returns>
        public static string CreateSelectStatement(SchemaObject tbl, TableColumn[] cols, Filter[] filters)
        {
            System.Text.StringBuilder buffer = new StringBuilder();
            buffer.Append("Select \n");
            for (int i = 0; i < cols.Length; i++)
            {
                buffer.Append(StoredProcUtil.GetProviderSpecificLeftEscape(tbl.Provider) +
                    cols[i].ColumnName +
                    StoredProcUtil.GetProviderSpecificRightEscape(tbl.Provider));
                if (i < cols.Length - 1)
                {
                    buffer.Append(",\n");
                }
            }
            buffer.Append("\n");
            if (tbl.Provider == DataProviderType.MSSQL)
            {
                buffer.Append("From " +
                    StoredProcUtil.GetProviderSpecificLeftEscape(tbl.Provider) +
                    tbl.TableName +
                    StoredProcUtil.GetProviderSpecificRightEscape(tbl.Provider) +
                    "\n");
            }
            else if (tbl.Provider == DataProviderType.Oracle)
            {
                buffer.Append("From " +
                    StoredProcUtil.GetProviderSpecificLeftEscape(tbl.Provider) +
                    tbl.Schema +
                    StoredProcUtil.GetProviderSpecificRightEscape(tbl.Provider) +
                    "." +
                    StoredProcUtil.GetProviderSpecificLeftEscape(tbl.Provider) +
                    tbl.TableName +
                    StoredProcUtil.GetProviderSpecificRightEscape(tbl.Provider) +
                    "\n");
            }
            if (filters != null && filters.Length > 0)
            {
                buffer.Append("Where\n");
                for (int i = 0; i < filters.Length; i++)
                {
                    buffer.Append(StoredProcUtil.GetProviderSpecificLeftEscape(tbl.Provider) +
                        filters[i].FieldName +
                        StoredProcUtil.GetProviderSpecificRightEscape(tbl.Provider) +
                        "=");
                    string filterValue = string.Empty;
                    if (filters[i].Expression != null)
                    {
                        filterValue = filters[i].Expression.ToString();
                    }
                    switch (filters[i].DbType)
                    {
                        case System.Data.SqlDbType.Char:
                        case System.Data.SqlDbType.DateTime:
                        case System.Data.SqlDbType.NChar:
                        case System.Data.SqlDbType.NText:
                        case System.Data.SqlDbType.NVarChar:
                        case System.Data.SqlDbType.SmallDateTime:
                        case System.Data.SqlDbType.Text:
                        case System.Data.SqlDbType.VarChar:
                            filterValue = "'" + filterValue + "'";
                            break;
                        default:
                            break;
                    }
                    buffer.Append(filterValue);
                    if (i < filterValue.Length - 1)
                    {
                        buffer.Append(" and \n");
                    }
                }
            }
            return buffer.ToString();

        }
    }
}
